package com.hongdee.crm.workitem.service.impl;

import com.hongdee.common.dao.IHibernateDao;
import com.hongdee.common.dao.IImpHibernateDao;
import com.hongdee.common.dao.IImpJdbcDao;
import com.hongdee.common.dao.IJdbcDao;
import com.hongdee.common.util.TokenUtil;
import com.hongdee.crm.workitem.dao.ITCrmCarWorkItemDao;
import com.hongdee.crm.workitem.entity.TCrmCarWorkItem;
import com.hongdee.crm.workitem.service.ITCrmCarWorkItemService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 名  称:TCrmCarWorkItemServiceImpl
 * 类  型: class
 * 描  述:
 * 路  径:
 * 作  者: 术洪超
 * 日  期:2017-02-22 17:51
 */
@Service
public class TCrmCarWorkItemServiceImpl implements ITCrmCarWorkItemService {

    private String tabName = "T_CRM_CAR_WORKITEM";
    private String EXIST_DATA_SQL = "select * from " + tabName + " where item_code  = ? and order_code = ?  and company_id = ? limit 1";
    @Autowired
    private IImpJdbcDao iImpJdbcDao;

    @Autowired
    private IImpHibernateDao iImpHibernateDao;

    @Autowired
    private IJdbcDao iJdbcDao;

    @Autowired
    private IHibernateDao iHibernateDao;

    @Autowired
    private ITCrmCarWorkItemDao itCrmCarWorkItemDao;

    @Override
    public Map impPage(String uid, String start, String limit) throws Exception {
        String sql = "select * from " + tabName + " where 1=1";
        String oId = TokenUtil.getCompanyId(uid);
        List params = new ArrayList();
        if (!oId.isEmpty()) {
            sql += " and company_id = ?";
            params.add(oId);
        }
        return iImpJdbcDao.queryForPageMap(start, limit, sql, params.toArray());
    }

    @Override
    public void impDel(String id) throws Exception {
        String sql = "delete from TCrmCarWorkItem where id =:id ";
        Map params = new HashMap();
        params.put("id", id);
        this.iImpHibernateDao.executeHqlWithParams(sql, params);
    }


    @Override
    public void impConfirm(String id, String uid) throws Exception {
        //根据id查询销售实体
        Object obj = iImpHibernateDao.findEntity(TCrmCarWorkItem.class, id);
        if (obj == null) {
            throw new Exception("无对应数据！");
        }
        TCrmCarWorkItem workItem = (TCrmCarWorkItem) obj;
        Map existData;
        existData = this.iJdbcDao.queryForMapBySqlWithParams(EXIST_DATA_SQL, workItem.getItemCode(), workItem.getOrderCode(), workItem.getCompanyId());
        //判断数据是否已存在
        if (existData.size() == 0) {
            //新数据
            workItem.setId(null);
            workItem.setCreateTime(new Date().getTime());
            workItem.setUpdateTime(new Date().getTime());
            workItem.setConfirmer(uid);
            workItem.setConfirmDate(new Date().getTime());
        } else {
            //已有数据
            workItem.setId(existData.get("id").toString());
            workItem.setUpdateTime(new Date().getTime());
            workItem.setConfirmer(uid);
            workItem.setConfirmDate(new Date().getTime());
        }
        this.iHibernateDao.saveOrUpdateEntity(workItem);
        //保存或更新
        this.impDel(id);

    }

    /**
     * @param data 批量数据
     * @return Map
     * @throws Exception
     * @desc 维修工单批量确认
     * @author 徐钦勇
     */
    @Override
    public void multiImpConfirm(List<Map> data, String uid) throws Exception {
        TCrmCarWorkItem workItem;
        Map existData;

        List<Object> idList = new ArrayList<>();
        for (Map orderId : data) {
            String idStr = String.valueOf(orderId.get("id"));
            //根据id查询销售实体
            Object obj = iImpHibernateDao.findEntity(TCrmCarWorkItem.class, idStr);
            if (obj == null) {
                throw new Exception("无效的数据！");
            }
            workItem = (TCrmCarWorkItem) obj;
            idList.add(workItem.getId());
            //根据vin判断主表立是否已存在
            existData = this.iJdbcDao.queryForMapBySqlWithParams(EXIST_DATA_SQL, workItem.getItemCode(), workItem.getOrderCode(), workItem.getCompanyId());
            if (existData.size() == 0) {
                //新数据
                workItem.setId(null);
                workItem.setCreateTime(new Date().getTime());
                workItem.setUpdateTime(new Date().getTime());
                workItem.setConfirmer(uid);
                workItem.setConfirmDate(new Date().getTime());
            } else {
                //已有数据
                workItem.setId(existData.get("id").toString());
                workItem.setUpdateTime(new Date().getTime());
                workItem.setConfirmer(uid);
                workItem.setConfirmDate(new Date().getTime());
            }
            //保存或更新
            this.iHibernateDao.saveOrUpdateEntity(workItem);

        }

        this.iImpJdbcDao.batchDel(tabName, "id", idList);
        idList = null;
    }

    /**
     * @return Map
     * @throws Exception
     * @desc 维修工单全部确认
     * @author 徐钦勇
     */
    @Override
    public void impConfirmAll(String uid) throws Exception {

        //获取公司 ID
        String companyId = TokenUtil.getCompanyId(uid);

        //  //目标数据库
        String targetSchema = iHibernateDao.Schema();
        //来源数据库
        String sourceSchema = iImpHibernateDao.Schema();

        if (StringUtils.isEmpty(targetSchema) || StringUtils.isEmpty(sourceSchema)) {
            throw new Exception("未获取到数据库实例请联系管理员");
        }

        StringBuffer updateSQL = new StringBuffer();
        updateSQL.append(" update " + targetSchema + ".t_crm_car_workitem c1,   ");
        updateSQL.append("        " + sourceSchema + ".t_crm_car_workitem c2 ");
        updateSQL.append("    SET c1.balance_code= c2.balance_code,");
        updateSQL.append("        c1.brand= c2.brand, ");
        updateSQL.append("        c1.company_id= c2.company_id, ");
        updateSQL.append("        c1.confirm_date= c2.confirm_date, ");
        updateSQL.append("        c1.confirmer= c2.confirmer, ");
        updateSQL.append("        c1.cost_time= c2.cost_time, ");
        updateSQL.append("        c1.fee_type= c2.fee_type, ");
        updateSQL.append("        c1.item_code= c2.item_code, ");
        updateSQL.append("        c1.item_name= c2.item_name, ");
        updateSQL.append("        c1.maintain_type= c2.maintain_type, ");
        updateSQL.append("        c1.maintaintype_id= c2.maintaintype_id, ");
        updateSQL.append("        c1.model= c2.model, ");
        updateSQL.append("        c1.open_date= c2.open_date, ");
        updateSQL.append("        c1.order_code= c2.order_code, ");
        updateSQL.append("        c1.order_type= c2.order_type, ");
        updateSQL.append("        c1.plate_num= c2.plate_num, ");
        updateSQL.append("        c1.receptor= c2.receptor, ");
        updateSQL.append("        c1.series= c2.series, ");
        updateSQL.append("        c1.status= c2.status, ");
        updateSQL.append("        c1.total_fee= c2.total_fee, ");
        updateSQL.append("        c1.unit_fee= c2.unit_fee, ");
        updateSQL.append("        c1.vin= c2.vin, ");
        updateSQL.append("        c1.work_kind= c2.work_kind, ");
        updateSQL.append("        c1.worker= c2.worker, ");
        updateSQL.append("        c1.product_name= c2.product_name, ");
        updateSQL.append("        c1.run_mile= c2.run_mile ");
        updateSQL.append("  where c1.company_id <=> c2.company_id  ");
        updateSQL.append("    and c1.order_code <=> c2.order_code  ");
        updateSQL.append("    and c2.item_code <=> c1.item_code  ");
        updateSQL.append("    and c2.item_name <=> c1.item_name  ");
        updateSQL.append("    and c1.vin <=> c2.vin and c1.company_id <=> '" + companyId + "' ");

        //删除已存在记录
        StringBuffer delExistSQL = new StringBuffer();
        String targeTable = targetSchema + ".t_crm_car_workitem";
        String sourceTable = sourceSchema + ".t_crm_car_workitem";

        delExistSQL.append("delete from " + sourceTable + " USING " + targeTable + "," + sourceTable + " WHERE ");
        delExistSQL.append(" " + targeTable + ".company_id <=> " + sourceTable + ".company_id  and " + targeTable + ".order_code  <=> " + sourceTable + ".order_code  and " + targeTable + ".company_id <=> '" + companyId + "' and " + targeTable + ".item_code  <=> " + sourceTable + ".item_code ");

        // delExistSQL.append("delete from " + sourceSchema + ".t_crm_car_workitem WHERE EXISTS (select 1 from " + targetSchema + ".t_crm_car_workitem o1 where o1.company_id = company_id  and o1.order_code  = order_code ) and company_id ='" + companyId + "'");


        StringBuffer insertSQL = new StringBuffer();

        StringBuffer selectField = new StringBuffer();
        selectField.append(" id, ");
        selectField.append(" create_time, ");
        selectField.append(" create_user, ");
        selectField.append(" update_time, ");
        selectField.append(" update_user, ");
        selectField.append(" balance_code, ");
        selectField.append(" brand, ");
        selectField.append(" company_id, ");
        selectField.append(" confirm_date, ");
        selectField.append(" confirmer, ");
        selectField.append(" cost_time, ");
        selectField.append(" fee_type, ");
        selectField.append(" item_code, ");
        selectField.append(" item_name, ");
        selectField.append(" maintain_type, ");
        selectField.append(" maintaintype_id, ");
        selectField.append(" model, ");
        selectField.append(" open_date, ");
        selectField.append(" order_code, ");
        selectField.append(" order_type, ");
        selectField.append(" plate_num, ");
        selectField.append(" receptor, ");
        selectField.append(" series, ");
        selectField.append(" status, ");
        selectField.append(" total_fee, ");
        selectField.append(" unit_fee, ");
        selectField.append(" vin, ");
        selectField.append(" work_kind, ");
        selectField.append(" worker, ");
        selectField.append(" product_name, ");
        selectField.append(" run_mile");

        insertSQL.append(" insert into " + targetSchema + ".t_crm_car_workitem(").append(selectField.toString()).append(") ").append(" select ");
        insertSQL.append(selectField.toString()).append(" from " + sourceSchema + ".t_crm_car_workitem where  company_id = '" + companyId + "'");


        //更新记录数
        int upRowCount = iJdbcDao.update(updateSQL.toString());
        System.out.println("-------------------更新维修项目数---------" + upRowCount);
        //删除更新记录数
        int delRowCount = iJdbcDao.update(delExistSQL.toString());
        System.out.println("-------------------删除维修项目数---------" + delRowCount);

        //新增记录数
        int addRowCount = iJdbcDao.update(insertSQL.toString());
        System.out.println("-------------------新增项目数---------" + addRowCount);

        //删除新增记录数
        int delAddRowCount = iJdbcDao.update(delExistSQL.toString());

        System.out.println("-------------------删除新增项目数---------" + delAddRowCount);


//        //编写查询源数据 HQL
//        String hql = "from TCrmCarWorkItem where companyId = :companyId";
//        //设置参数
//        Map<String, Object> params = new HashMap();
//        params.put("companyId", companyId);
//        //查询公司下的全部车辆
//
//        List<TCrmCarWorkItem> workOrderList;
//        String pageSize = "1000";
//        //每50条保存一次
//        List<Object> idList = new ArrayList<>();
//        Map existData;
//        String tempId;
//        while (true) {
//            //第次500条
//            workOrderList = iImpHibernateDao.findEntityPage(hql, params, "0", pageSize);
//            if (null == workOrderList || workOrderList.size() == 0) {
//                //无数据时结束
//                break;
//            }
//            for (TCrmCarWorkItem workItem : workOrderList) {
//                //根据vin判断主表立是否已存在
//                existData = this.iJdbcDao.queryForMapBySqlWithParams(EXIST_DATA_SQL, workItem.getItemCode(), workItem.getOrderCode(), workItem.getCompanyId());
//                tempId = workItem.getId();
//                idList.add(tempId);
//                if (existData.size() == 0) {
//                    //新数据
//                    workItem.setId(null);
//                    workItem.setCreateTime(new Date().getTime());
//                    workItem.setUpdateTime(new Date().getTime());
//                    workItem.setConfirmer(uid);
//                    workItem.setConfirmDate(new Date().getTime());
//                } else {
//                    //已有数据
//                    workItem.setId(existData.get("id").toString());
//                    workItem.setUpdateTime(new Date().getTime());
//                    workItem.setConfirmer(uid);
//                    workItem.setConfirmDate(new Date().getTime());
//                }
//                //保存或更新
//                this.iHibernateDao.saveOrUpdateEntity(workItem);
//
//            }
//            this.iImpJdbcDao.batchDel(tabName, "id", idList);
//            idList.clear();
        //  }
    }

    /**
     * @param uid       登录用户id
     * @param orderCode 工单号
     * @return List
     * @throws Exception
     * @desc 根据工单号和公司查询工单项目
     * @author 徐钦勇
     */
    @Override
    public List page(String uid, String orderCode) throws Exception {
        String companyId = TokenUtil.getCompanyId(uid);
        String sql = "select * from " + tabName + " where company_id = ? and order_code = ? ";
        List params = new ArrayList();
        params.add(companyId);
        params.add(orderCode);
        return this.itCrmCarWorkItemDao.list(sql, params.toArray());
    }

    /**
     * @return
     * @throws Exception
     * @desc 删除全部待导入数据
     * @author 术洪超
     */
    @Override
    public void impDelAll(String uid) throws Exception {

        Map map = new HashMap();
        String companyId = TokenUtil.getCompanyId(uid);
        String hql = " delete from TCrmCarWorkItem where companyId =:companyId";
        map.put("companyId", companyId);
        //System.out.println(companyId);

        this.iImpHibernateDao.executeHqlWithParams(hql, map);
    }

    @Override
    public void impDels(List<Map<Object, String>> data, String uid) throws Exception {
        List<Object> idList = new ArrayList();
        for (Map map : data) {
            idList.add(map.get("id"));
        }
        this.iImpJdbcDao.batchDel(tabName, "id", idList);
    }

    /**
     * @Name: 工单项目明细查询
     * @Author: Liu XiaoLong
     * @Date: 2017/9/14 19:44
     * @Remark:
     */
    @Override
    public Map workItemQuery(String uid, Map map, String start, String limit) throws Exception {

            List params = new ArrayList();
            String sql = "SELECT create_time createTime,item_code itemCode,item_name itemName,maintain_type maintainType,order_code orderCode" +
                    " FROM t_crm_car_workitem WHERE company_id = ? ";
        if(!StringUtils.isEmpty(map.get("companyId"))){
            params.add(map.get("companyId"));
        }
        else {
            String a = TokenUtil.getCompanyId(uid);
            params.add(a);
        }

            if(!StringUtils.isEmpty(map.get("start"))){
                sql  += "AND FROM_UNIXTIME(create_time/1000,'%Y-%m-%d') >= ? ";
                params.add(map.get("start"));
               /* sql = sql + "AND create_time >= ? ";
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                Date date = simpleDateFormat.parse((String) map.get("start"));
                long ts = date.getTime();
                params.add(ts);*/
            }
            if(!StringUtils.isEmpty(map.get("stop"))){
                sql  += "AND FROM_UNIXTIME(create_time/1000,'%Y-%m-%d') <= ? ";
                params.add(map.get("stop"));
               /* sql = sql + "AND create_time <= ? ";
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                Date date = simpleDateFormat.parse((String) map.get("stop"));
                long ts = date.getTime();
                params.add(ts);*/
            }

            return iJdbcDao.queryForPageMap(start,limit,sql,params.toArray());
    }
}

